﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Configuration;

namespace DeviceDebugger.DataAccess
{
    #region 数据库操作类
    /// <summary>
    /// 通用数据库访问类，封装了对数据库的常见操作
    /// 项目需要引用对应数据库的程序集
    ///</summary>
    public class DbUtility
    {
        public string ConnectionString { get; set; } = string.Format("Data Source={0};Version=3;", "DataBase/Data.db");
        private DbProviderFactory providerFactory;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="providerType">数据库类型枚举，参见<paramref name="providerType"/></param>
        public DbUtility(DbProviderType providerType)
        {
            providerFactory = ProviderFactory.GetDbProviderFactory(providerType);
            if (providerFactory == null)
            {
                throw new ArgumentException("无法为providerType的给定值加载DbProviderFactory类型！");
            }
        }
        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的增删改的SQL语句</param>   
        /// <param name="parameters">执行增删改语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型，CommandType是枚举类型，默认为Text</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, List<DbParameter> parameters, CommandType commandType = CommandType.Text)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                int affectedRows = command.ExecuteNonQuery();
                command.Connection.Close();
                return affectedRows;
            }
        }

        /// <summary>   
        /// 执行一个查询语句，返回一个关联的DataReader实例   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型，CommandType是枚举类型，默认为Text</param>
        /// <returns></returns> 
        public DbDataReader ExecuteReader(string sql, List<DbParameter> parameters, CommandType commandType = CommandType.Text)
        {
            DbCommand command = CreateDbCommand(sql, parameters, commandType);
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型，CommandType是枚举类型，默认为Text</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, List<DbParameter> parameters, CommandType commandType = CommandType.Text)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }

        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataSet
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型，CommandType是枚举类型，默认为Text</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, List<DbParameter> parameters, CommandType commandType = CommandType.Text)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>   
        /// 执行一个查询语句，返回查询结果的首行首列
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型，CommandType是枚举类型，默认为Text</param>
        /// <returns></returns>   
        public Object ExecuteScalar(string sql, List<DbParameter> parameters, CommandType commandType = CommandType.Text)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                object result = command.ExecuteScalar();
                command.Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 通过事务批量执行非查询SQL语句
        /// </summary>
        /// <param name="strSQLs">需要批量执行的SQL</param>
        public bool ExecuteNonQueryTransaction(List<string> ListSql)
        {
            return ExecuteNonQueryTransaction(ListSql, null);
        }

        /// <summary>
        /// 通过事务批量执行非查询SQL语句
        /// </summary>
        /// <param name="strSQLs">需要批量执行的SQL</param>
        ///  <param name="listParameters">需要批量执行的SQL的参数</param>
        public bool ExecuteNonQueryTransaction(List<string> ListSql, List<List<DbParameter>> listParameters)
        {
            bool bResult = false;
            DbConnection connection = providerFactory.CreateConnection();
            connection.ConnectionString = ConnectionString;
            connection.Open();
            using (DbTransaction transaction = connection.BeginTransaction())
            {
                using (DbCommand command = providerFactory.CreateCommand())
                {
                    try
                    {
                        command.Transaction = transaction;
                        for (int i = 0; i < ListSql.Count; i++)
                        {
                            command.CommandText = ListSql[i];
                            command.CommandType = CommandType.Text;
                            if (listParameters != null && listParameters.Count > 0)
                            {
                                if (!(listParameters[i] == null || listParameters[i].Count == 0))
                                {
                                    foreach (DbParameter parameter in listParameters[i])
                                    {
                                        command.Parameters.Add(parameter);
                                    }
                                }
                            }
                            command.ExecuteNonQuery();
                        }
                        transaction.Commit();
                        bResult = true;
                    }
                    catch
                    {
                        bResult = false;
                        transaction.Rollback();
                    }
                }

            }
            connection.Close();
            return bResult;
        }

        /// <summary>
        /// 创建执行语句的参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数的值</param>
        /// <param name="parameterDirection">参数类型，ParameterDirection是枚举类型，默认为输入参数</param>
        /// <returns></returns>
        public DbParameter CreateDbParameter(string name, object value, ParameterDirection parameterDirection = ParameterDirection.Input)
        {
            DbParameter parameter = providerFactory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = parameterDirection;
            return parameter;
        }

        /// <summary>
        /// 创建执行语句的参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数的值</param>
        /// <param name="dbtype">参数对应数据库的类型</param>
        /// <param name="parameterDirection">参数类型，ParameterDirection是枚举类型，默认为输入参数</param>
        /// <returns></returns>
        public DbParameter CreateDbParameter(string name, object value, DbType dbtype, ParameterDirection parameterDirection = ParameterDirection.Input)
        {
            DbParameter parameter = providerFactory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.DbType = dbtype;
            parameter.Direction = parameterDirection;
            return parameter;
        }

        /// <summary>
        /// 创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        private DbCommand CreateDbCommand(string sql, List<DbParameter> parameters, CommandType commandType)
        {
            DbConnection connection = providerFactory.CreateConnection();
            DbCommand command = providerFactory.CreateCommand();
            connection.ConnectionString = ConnectionString;
            command.CommandText = sql;
            command.CommandType = commandType;
            command.Connection = connection;
            if (!(parameters == null || parameters.Count == 0))
            {
                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }
    }
    #endregion

    #region DbProviderFactory工厂类

    /// <summary>
    /// 数据库类型枚举
    /// </summary>
    public enum DbProviderType : byte
    {
        SqlServer,
        MySql,
        SQLite,
        Oracle,
    }
    /// <summary>
    /// DbProviderFactory工厂类
    /// </summary>
    public class ProviderFactory
    {
        private static Dictionary<DbProviderType, string> providerInvariantNames = new Dictionary<DbProviderType, string>();
        private static Dictionary<DbProviderType, DbProviderFactory> providerFactoies = new Dictionary<DbProviderType, DbProviderFactory>(20);
        static ProviderFactory()
        {
            //加载已知的数据库访问类的程序集
            providerInvariantNames.Add(DbProviderType.SqlServer, "System.Data.SqlClient");
            providerInvariantNames.Add(DbProviderType.Oracle, "Oracle.DataAccess.Client");
            providerInvariantNames.Add(DbProviderType.MySql, "MySql.Data.MySqlClient");
            providerInvariantNames.Add(DbProviderType.SQLite, "System.Data.SQLite");
        }
        /// <summary>
        /// 获取指定数据库类型对应的程序集名称
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        public static string GetProviderInvariantName(DbProviderType providerType)
        {
            return providerInvariantNames[providerType];
        }
        /// <summary>
        /// 获取指定类型的数据库对应的DbProviderFactory
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType)
        {
            //如果还没有加载，则加载该DbProviderFactory
            if (!providerFactoies.ContainsKey(providerType))
            {
                providerFactoies.Add(providerType, ImportDbProviderFactory(providerType));
            }
            return providerFactoies[providerType];
        }
        /// <summary>
        /// 加载指定数据库类型的DbProviderFactory
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType)
        {
            string providerName = providerInvariantNames[providerType];
            DbProviderFactory factory;
            try
            {
                //从全局程序集中查找
                factory = DbProviderFactories.GetFactory(providerName);
            }
            catch (ArgumentException ex)
            {
                factory = null;
            }
            return factory;

        }
    }
    #endregion

}
